package powell.nflpredictionproject.database;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/*
 * This class populates the database created in the BuildDatabase class with 
 * statistics provided to me in a .csv files that I found online. The .csv files
 * are location in the project folder.
 */
public class PopulateDatabase
{

    /*
     * Variable declarations on the class level.
     */
    private int count2012 = 0;
    private int count2013 = 0;

    /*
     * This method reads from the file storing the statistics read into arrays
     * that hold these statistics. The method then places the values in the
     * arrays into the appropriate areas of the database.
     * 
     * @throws FileNotFoundException, SQLException
     */
    public void Populate() throws FileNotFoundException, SQLException
    {
        File inFile = new File("Stats.txt");
        Scanner readFile = new Scanner(inFile);
        String[] line;
        File inFile2013 = new File("2013Stats.txt");
        Scanner readFile2013 = new Scanner(inFile2013);
        String[] line2013;
        Connection conn = DriverManager.getConnection("jdbc:derby:NFLStatsDatabase");
        Statement stmt = conn.createStatement();



        while (readFile.hasNextLine())
        {

            line = readFile.nextLine().split(",");

            for (int index = 0; index < line.length; index++)
            {
                System.out.println(line[index]);
            }

            if (count2012 == 1)
            {
                stmt.execute("Insert into STATS2012 Values("
                        + "'" + line[1] + "', "
                        + "" + line[2] + ", "
                        + "" + line[3] + ", "
                        + "'" + line[4] + "', "
                        + "" + line[5] + ", "
                        + "" + line[6] + ", "
                        + "" + line[7] + ", "
                        + "" + line[8] + ", "
                        + "" + line[9] + ", "
                        + "" + line[10] + ", "
                        + "" + line[11] + ", "
                        + "" + line[12] + ", "
                        + "" + line[13] + ", "
                        + "" + line[14] + ", "
                        + "'" + line[15] + "', "
                        + "" + line[16] + ", "
                        + "" + line[18] + ", "
                        + "" + line[19] + ", "
                        + "'" + line[20] + "', "
                        + "" + line[22] + ", "
                        + "" + line[25]
                        + ")");
            }
            count2012 = 1;
        }

        while (readFile2013.hasNextLine())
        {

            line2013 = readFile2013.nextLine().split(",");

            for (int index = 0; index < line2013.length; index++)
            {
                System.out.println(line2013[index]);
            }

            if (count2013 == 1)
            {
                stmt.execute("Insert into STATS2013 Values("
                        + "'" + line2013[1] + "', "
                        + "" + line2013[2] + ", "
                        + "" + line2013[3] + ", "
                        + "'" + line2013[4] + "', "
                        + "" + line2013[5] + ", "
                        + "" + line2013[6] + ", "
                        + "" + line2013[7] + ", "
                        + "" + line2013[8] + ", "
                        + "" + line2013[9] + ", "
                        + "" + line2013[10] + ", "
                        + "" + line2013[11] + ", "
                        + "" + line2013[12] + ", "
                        + "" + line2013[13] + ", "
                        + "" + line2013[14] + ", "
                        + "'" + line2013[15] + "', "
                        + "" + line2013[16] + ", "
                        + "" + line2013[18] + ", "
                        + "" + line2013[19] + ", "
                        + "'" + line2013[20] + "', "
                        + "" + line2013[22] + ", "
                        + "" + line2013[25]
                        + ")");
            }
            count2013 = 1;
        }
        conn.close();
    }
}
